package br.skytef.stmanager.daos;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import br.skytef.stmanager.entities.ComercialEntity;
import br.skytef.stmanager.utils.Pool;

public class ComercialDAO{

	private java.sql.Connection conexao;

	public boolean inserir(ComercialEntity comercial){
		try {
			conexao = Pool.getConnection();
			String query = "INSERT INTO Comercial(nome, telefone, email, data_inclusao, comissao) VALUES (?,?,?,?,?);";
			PreparedStatement preparedStmt = conexao.prepareStatement(query);
			preparedStmt.setString 		(1, comercial.getNome());
			preparedStmt.setString 		(2, comercial.getTelefone());
			preparedStmt.setString 		(3, comercial.getEmail());
			preparedStmt.setTimestamp	(4, comercial.getData_inclusao());
			preparedStmt.setInt 		(5, comercial.getComissao());
			preparedStmt.execute();
			conexao.close();
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	public boolean alterar(ComercialEntity comercial){
		try {
			conexao = Pool.getConnection();
			String query = "UPDATE INTO Comercial SET nome=?, telefone=?, email=?, data_inclusao=?, comissao=? WHERE id=?;";
			PreparedStatement preparedStmt = conexao.prepareStatement(query);
			preparedStmt.setString 		(1, comercial.getNome());
			preparedStmt.setString 		(2, comercial.getTelefone());
			preparedStmt.setString 		(3, comercial.getEmail());
			preparedStmt.setTimestamp	(4, comercial.getData_inclusao());
			preparedStmt.setInt 		(5, comercial.getComissao());
			preparedStmt.setInt 		(6, comercial.getId());
			preparedStmt.execute();
			conexao.close();
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	public boolean excluir(ComercialEntity comercial){
		try {
			conexao = Pool.getConnection();
			String query = "DELETE FROM Comercial WHERE id=?;";
			PreparedStatement preparedStmt = conexao.prepareStatement(query);
			preparedStmt.setInt 		(1, comercial.getId());
			preparedStmt.execute();
			conexao.close();
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	public ComercialEntity buscar(String nome){
		ComercialEntity comercial = null;
		try {

			conexao = Pool.getConnection();
			Statement st = conexao.createStatement();
			ResultSet rs = st.executeQuery("SELECT * FROM Comercial WHERE nome LIKE '%"+ nome +"%';");
			if (rs.next()) {                    
				comercial = new ComercialEntity();
				comercial.setId(rs.getInt("id"));
				comercial.setNome(rs.getString("nome"));
				comercial.setTelefone(rs.getString("telefone"));
				comercial.setEmail(rs.getString("email"));
				comercial.setData_inclusao(rs.getTimestamp("data_inclusao"));
				comercial.setComissao(rs.getInt("comissao"));
			}
			conexao.close();			
		}catch (SQLException e) {
			System.out.println("SQL erro: "+ e);
		}
		return comercial;
	}

}
